BUILTIN\Administrators in Sysadmin Role

Issue

In many installations (particularly in enterprise scenarios) administrators like to see the distinction between the roles of the operating system administrator and the SQL Server™ administrator. In such scenarios it might be useful to remove the local Windows® administrators' membership in the SQL sysadmin role. However, in smaller deployments of SQL Server or in local Microsoft® SQL Server Desktop Engine (MSDE) or WMSDE scenarios, it may be acceptable to ignore this check and leave the local administrators inside the sysadmin role.

Solution

Remove BUILTIN\Administrators from the Sysadmin role.

Note:

• There are special circumstances that require administrators to belong to the Sysadmin role. These circumstances are outlined in the following Microsoft Knowledge Base articles:

SQL Server Agent Does Not Start and Displays Error 18456 (237604)

How to Prevent Windows NT Administrators from Administering a Clustered SQL Server (263712)

IsAlive Check Does Not Run Under the Context of the BUILTIN\Administrators Account (291255)

Microsoft Search Service May Cause 100% CPU Usage if BUILTIN\Administrators Login is Removed (295034)

Important

• Before removing the BUILTIN\Administrators group from SQL Server, it is recommended that there be at least one other login that has administrative capabilities in SQL Server. Further, certain applications may rely on the ability of the BUILTIN\Administrators group to be able to administer SQL Server. Before proceeding with the solution below, ensure that your applications can work in this configuration.

Instructions

1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
2. In SQL Server Enterprise Manager, double-click SQL Server Group, and then double-click the SQL Server that you want to secure.
3. Click the Security folder, click Server Roles, and then double-click System Administrators in the right pane.
4. In the Server Role Properties dialog box, click BUILTIN\Administrators, and then click Remove.

In scenarios where there is no Enterprise Manager (as in the case of MSDE or WMSDE) or when it is desired to perform the above actions through a script, the following can be done:

1. Open a command prompt window.
2. Use the osql utility to connect to the SQL instance using a valid login.
3. Issue the following command to remove the BUILTIN\Administrators group from the sysadmin role:
1. sp_dropsrvrolemember 'builtin\administrators','sysadmin'
2. go

Additional Information

SQL Server 7.0 Security

Microsoft SQL Server 2000 Security

©2002-2004 Microsoft Corporation. All rights reserved.